package com.vp.org.constant;

public interface BookSql {

    // 查询上传时间最新的10本书
    String QUERY_BY_HOT = "SELECT ID, NAME, IMG, DISCOUNT_PRICE PRICE FROM BOOK_INFO ORDER BY UPLOAD_TIME DESC LIMIT 0, 10";

    // 根据条件查
    String QUERY_BY_CONDITION = "SELECT ID, NAME, IMG, DISCOUNT_PRICE PRICE FROM BOOK_INFO WHERE TYPE = ?";

    // 查询打折后价格最低的10本书
    String QUERY_BY_DISCOUNT = "SELECT ID, NAME, IMG, PRICE, DISCOUNT FROM BOOK_INFO ORDER BY PRICE * DISCOUNT LIMIT 0, 10";

    // 通过id查详情
    String QUERY_BY_ID = "SELECT ID, NAME, IMG, PRICE, DISCOUNT, DETAILS FROM BOOK_INFO WHERE ID = ?";

    // 根据id查询当前用户购物数
    String QUERY_FOR_CARTS = "SELECT COUNT(?) COUNT FROM SHOPPING_CART";

    // 查当前书的评论
    String QUERY_REMARK_BY_ID = "SELECT U.USER_ID ID, U.PICTURE, U.NICKNAME, R.CONTENT, R.RANK, R.BUY_TIME BUY, R.REMARK_TIME REMARK FROM REMARK R, USER_INFO U, BOOK_INFO B WHERE R.BOOK_ID = B.ID AND R.USER_ID = U.USER_ID AND B.ID = ?";

    // 通过书名模糊查询
    String QUERY_BY_NAME = "SELECT ID, NAME, IMG, PRICE, DISCOUNT, DETAILS FROM BOOK_INFO WHERE NAME LIKE ? LIMIT 0, 1";

    // 收藏此书
    String COLLECT_BOOK = "INSERT INTO COLLECT (BOOK_ID, USER_ID) VALUES (?, ?)";

    // 查询所有收藏
    String QUERY_COLLECT = "SELECT ID, BOOK_ID FROM COLLECT WHERE USER_ID = ?";

    // 删除收藏通过id
    String DELETE = "DELETE FROM COLLECT WHERE ID = ?";
}